﻿using System;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Excelhelper
{

    public class ExcelWriter
    {
        //string filePath = "E:\\植物工厂环控系统\\Datasets\\Envs_Data.xlsx"; // Excel 文件保存路径
        private string _filePath;

        public ExcelWriter(string filePath)
        {
            _filePath = filePath;
        }

        // 写入传感器数据到 Excel 文件
        public void WriteDataToExcel(string[] sensorData)
        {
            // 如果文件不存在，则创建文件
            if (!File.Exists(_filePath))
            {
                CreateNewExcelFile();
            }

            // 打开 Excel 文件进行写入
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(_filePath, true))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                // 找到最后一行，准备在下一行写入数据
                uint rowIndex = (uint)(sheetData.Elements<Row>().Count() + 1);

                // 如果是第一行，写入列标题（温度，湿度）
                if (rowIndex == 2)
                {
                    Row headerRow = new Row { RowIndex = rowIndex };
                    Cell cell1 = CreateCell("A" + rowIndex, "时间");
                    Cell cell2 = CreateCell("B" + rowIndex, "温度");
                    Cell cell3 = CreateCell("C" + rowIndex, "湿度");
                    Cell cell4 = CreateCell("D" + rowIndex, "光强");
                    headerRow.Append(cell1, cell2, cell3, cell4);
                    sheetData.Append(headerRow);
                }
                // 获取当前时间作为时间戳
                string timeStamp = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                // 写入数据
                rowIndex++;
                Row dataRow = new Row { RowIndex = rowIndex };
                Cell timeCell = CreateCell("A" + rowIndex, timeStamp);
                Cell temperatureCell = CreateCell("B" + rowIndex, sensorData[0].Split(':')[1].Trim()); // 取温度值
                Cell humidityCell = CreateCell("C" + rowIndex, sensorData[1].Split(':')[1].Trim()); // 取湿度值
                //Cell luminosityCell = CreateCell("D" + rowIndex, sensorData[2].Split(':')[1].Trim()); // 取湿度值
                dataRow.Append(timeCell, temperatureCell, humidityCell);
                //dataRow.Append(timeCell, temperatureCell, humidityCell, luminosityCell);
                sheetData.Append(dataRow);

                // 保存更改
                workbookPart.Workbook.Save();

            }
        }


        // 创建新的 Excel 文件
        private void CreateNewExcelFile()
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(_filePath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                // 创建工作表
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());

                // 创建工作表
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
                sheets.Append(sheet);

                // 保存工作簿
                workbookPart.Workbook.Save();
            }
        }

        // 创建单元格
        private static Cell CreateCell(string cellReference, string value)
        {
            Cell cell = new Cell
            {
                CellReference = cellReference,
                DataType = CellValues.String,
                CellValue = new CellValue(value)
            };
            return cell;
        }
    }
}
